

DROP TABLE ASM_INSOLVENTI_TIA_0;
creATE TABLE ASM_INSOLVENTI_TIA_0 AS;
truncate table ASM_INSOLVENTI_TIA_0 ;
insert into ASM_INSOLVENTI_TIA_0 

SELECT 

SO.SOG_ID ,SO.SOG_RAGSOC RAGIONE_SOCIALE,

UBI.DECODE_INDID IND_IMMOB,
 RF.SOG_RAGSOC RAGIONE_SOCIaLE_SPE,
RF.DECODE_INDIDSPEFAT INDIRIZZO_SPEDIZIONE,
SO.SOG_PARIVA PARTITAIVA,
SO.SOG_CODFIS CODICE_FISCALE,
an.codice_tipo_evento,an.data_evento,
     (select max(x.inc_dtpag) from v_scadenziario x where  x.az_id='01' and x.STA_IDSYS=21 and x.sog_id=so.sog_id) data_ultimo_pagamento

        FROM 
        v_scadenziario ,
        v_soggetto so ,
        V_RAGFAT RF,
        V_CONUBIFRN UBI,
        db1.asm_anagrafe_terni_old@pgu3 an

               WHERE 

        v_scadenziario.az_id='01'
       
        and SO.sog_id=  v_scadenziario.SOG_ID
        --AND  (SELECT DISTINCT 1  FROM V_SCaDENx.ZIARIO Y
                                        -- WHERE STA_IDSYS IN (21) AND SO.SOG_ID = Y.SOG_ID) IS NULL
          and ((select max(x.inc_dtpag) from v_scadenziario x where  x.az_id='01' and x.STA_IDSYS=21 and x.sog_id=so.sog_id) < to_date ('01012012','ddmmyyyy')
                       or (select max(x.inc_dtpag) from v_scadenziario x where   x.az_id='01' and  x.STA_IDSYS=21 and x.sog_id=so.sog_id) is null)
          
         --AND (select DISTINCT 3 from asm_morosi_poste_EXCEL Y WHERE  SO.SOG_ID = Y.UTENTE) IS NULL
         --and rata_dtsca between   sysdate-365*10  and    sysdate-365*2
         AND RF.AZ_ID='01'
         AND RF.SOG_ID=SO.SOG_ID
         AND RF.STA_IDSYS=2
         AND RF.VER_DTFINE = TO_DATE('31129999','DDMMYYYY')
         AND ubi.VER_DTFINE = TO_DATE('31129999','DDMMYYYY')
         AND UBI.SOG_IDCLIFIN=SO.SOG_ID
         AND UBI.STA_IDSYS=2
         AND UBI.AZ_ID ='01'
         and so.sog_codfis=an.codice_fiscale(+)
   -- and so.sog_id IN (3602596) --,1143430)
             group by
             SO.SOG_ID ,SO.SOG_RAGSOC ,

UBI.DECODE_INDID ,
 RF.SOG_RAGSOC ,
RF.DECODE_INDIDSPEFAT ,
SO.SOG_PARIVA ,
SO.SOG_CODFIS ,
an.codice_tipo_evento,an.data_evento 
  ;